package com.campus_information_portal.dao;

import com.campus_information_portal.entity.Course;
import com.campus_information_portal.entity.CourseAcademy;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author 20155878 鄢前州
 * @Description 添加课程
 * @methodName
 * @returnType
 * @create 21:34 2018/4/29
 **/

public interface CourseDao {
    /**
     * @Description 添加课程
     * @author 20155878 鄢前州
     * @methodName addCourse
     * @returnType boolean
     * @create 21:34 2018/4/29
     **/

    @Insert("insert into course(course_name,course_num,academy_id,course_credithour,course_period,course_type) " +
            "values(#{course.courseName},#{course.courseNum},#{course.academyId},#{course.courseCredithour},#{course.coursePeriod},#{course.courseType})")
    public boolean addCourse(@Param("course") Course course, @Param("id") Integer id);/*这个id参数太迷，不加还有错，但数据库能增加数据，方法执行报错*/

    /**
     * @Description 通过课程ID查找课程
     * @author 20155878 鄢前州
     * @methodName queryCouseBycourse_id
     * @returnType Course
     * @create 21:36 2018/4/27
     **/
    @Select("select course.*,academy.academy_id,academy.academy_name " +
            "from course,academy " +
            "WHERE course_id=#{courseId} " +
            "AND course.academy_id=academy.academy_id")
    CourseAcademy queryCouseBycourse_id(@Param("courseId") int courseId);

    /**
     * @Description 查找所有课程
     * @author 20155878 鄢前州
     * @methodName queryAllCouse
     * @returnType 集合
     * @create 22:30 2018/4/27
     **/
    @Select("select * from course ")
    public List<Course> queryAllCouse();

    /**
     * @Description 隐式连课程、学院查询
     * @author 20155878 鄢前州
     * @methodName queryAllCouse2
     * @returnType List
     * @create 22:43 2018/5/6
     **/
    @Select("select course.* ,academy.academy_name " +
            "from course,academy " +
            "WHERE course.academy_id=academy.academy_id ")
    public List<CourseAcademy> queryAllCouse2();

    /**
     * @Description 通过课程id更新课程信息
     * @author 20155878 鄢前州
     * @methodName updateCourseBycourse_id
     * @returnType boolean
     * @create 22:11 2018/4/28
     **/
    @Update("update Course set " +
            "course_id = #{course.courseId}," +
            "course_name= #{course.courseName}," +
            "course_num= #{course.courseNum}," +
            "academy_id= #{course.academyId}," +
            "course_credithour= #{course.courseCredithour}," +
            "course_period= #{course.coursePeriod}," +
            "course_type= #{course.courseType} where course_id=#{course.courseId}"//注意数据库字段和实体类字段不相同
    )
    public boolean updateCourseBycourse_id(@Param("course") Course course);

    /**
     * @Description 按id删除课程
     * @author 20155878 鄢前州
     * @methodName deleteCourseBycourse_id
     * @returnType boolean
     * @create 22:46 2018/4/28
     **/
    @Delete("DELETE FROM Course WHERE course_id = #{courseId}")
    public boolean deleteCourseBycourse_id(@Param("courseId") Integer courseId);

    /**
     * @Description 写代码删除方便，按课程名称删除课程
     * @author 20155878 鄢前州
     * @methodName deleteCoursecourse_name
     * @returnType boolean
     * @create 23:10 2018/5/12
     **/
    @Delete("DELETE FROM Course WHERE course_name = #{courseName}")
    public boolean deleteCoursecourse_name(@Param("courseName") String courseName);

    /**
     * @Description 按课程名称查找课程
     * @author 20155878 鄢前州
     * @methodName queryCouseBycourse_name
     * @returnType Course
     * @create 10:27 2018/5/2
     **/
    @Select("select course.*,academy.academy_id,academy.academy_name " +
            "from course,academy " +
            "WHERE course_name=#{course.courseName} " +
            "AND course.academy_id=academy.academy_id")
    public CourseAcademy queryCouseBycourse_name(@Param("course") Course course);

    /**
     * @Description 按照课程代码查找课程
     * @author 20155878 鄢前州
     * @methodName queryCouseBycourse_num
     * @returnType Course
     * @create 21:49 2018/5/2
     **/
    @Select("select course.*,academy.academy_id,academy.academy_name " +
            "from course,academy " +
            "WHERE course_num=#{course.courseNum} " +
            "AND course.academy_id=academy.academy_id")
    public CourseAcademy queryCouseBycourse_num(@Param("course") Course course);

    /**
     * @Description 按课程类型查找课程
     * @author 20155878 鄢前州
     * @methodName queryCousecourse_type
     * @returnType list集合
     * @create 21:51 2018/5/2
     **/
    @Select("select course.*,academy.academy_id,academy.academy_name " +
            "from course ,academy " +
            "WHERE course_type=#{course.courseType} " +
            "AND course.academy_id=academy.academy_id")
    public List<CourseAcademy> queryCousecourse_type(@Param("course") Course course);


    /**
     * @Description 通过课程名称查询课程id
     * @author 20155878 鄢前州
     * @methodName querycourse_idBycourse_name
     * @returnType int
     * @create 11:05 2018/5/10
     **/
    @Select("select course_id from course where course_name=#{course_name}")
    public int querycourse_idBycourse_name(@Param("course_name") String course_name);

    /**
     * @param
     * @Description 不重复课程类型
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     * @create 2018/5/26 15:56
     **/
    @Select("select DISTINCT course_type from course ")
    public List<Course> queryAllCouseDistict();

    /**
     * @param
     * @Description 不重复通过课程课程类型查找课程名称
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     * @create 2018/5/26 15:56
     **/
    @Select("select DISTINCT course_name from course WHERE course_type=#{course.courseType} ")
    public List<Course> queryCousecourseType(@Param("course") Course course);

    /**
     * @param
     * @Description 通过课程名称课程类型查找课程id
     * @author 20155884 王晓阳
     * @methodName
     * @returnType
     * @create 2018/5/27 10:53
     **/
    @Select("select course_id from course where course_type=#{courseType} and course_name=#{courseName}")
    Course queryCouseId(@Param("courseType")String courseType,@Param("courseName")String courseName);
}
